package com.scuec.tourismwebsite.mapper;


import com.scuec.tourismwebsite.entity.UserOrder;
import com.scuec.tourismwebsite.vo.OrderUserBoutique;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface UserOrderMapper {
    //查找所有
    @Select("SELECT\n" +
            "    u.avatar,\n" +
            "    u.nickname,\n" +
            "    o.order_id,\n" +
            "    b.tour_name,\n" +
            "    b.price,\n" +
            "    o.order_date,\n" +
            "    o.total_amount,\n" +
            "    o.status\n" +
            "FROM\n" +
            "    user u\n" +
            "        JOIN\n" +
            "    user_order o ON u.user_id = o.user_id\n" +
            "        JOIN\n" +
            "    boutique_tour b ON o.boutique_id = b.boutique_id ORDER BY o.order_id")
    List<OrderUserBoutique> findOrderAll();

    //根据id删除
    @Delete("delete from user_order where order_id=#{order_id}")
    Integer deleteOrderById(int order_id);

    @Select("SELECT\n" +
            "    o.boutique_id,\n" +
            "    b.tour_name,\n" +
            "    COUNT(*) AS total_quantity,\n" +
            "    SUM(o.total_amount) AS total_amounts\n" +
            "FROM\n" +
            "    travel.user_order o\n" +
            "        JOIN\n" +
            "    boutique_tour b ON o.boutique_id = b.boutique_id\n" +
            "GROUP BY\n" +
            "    o.boutique_id,b.tour_name\n" +
            "ORDER BY\n" +
            "    total_quantity DESC")
    List<OrderUserBoutique> findOrderToAll();

    @Select("SELECT\n" +
            "    o.boutique_id,\n" +
            "    b.tour_name,\n" +
            "    COUNT(*) AS total_quantity,\n" +
            "    SUM(o.total_amount) AS total_amounts\n" +
            "FROM\n" +
            "    travel.user_order o\n" +
            "        JOIN\n" +
            "    boutique_tour b ON o.boutique_id = b.boutique_id\n WHERE b.tour_name LIKE CONCAT('%', #{tour_name}, '%')" +
            "GROUP BY\n" +
            "    o.boutique_id,b.tour_name\n" +
            "ORDER BY\n" +
            "    total_quantity DESC")
    List<OrderUserBoutique> findAnaByname(String tour_name);


    //根据nickname查询
    @Select("SELECT\n" +
            "    u.avatar,\n" +
            "    u.nickname,\n" +
            "    o.order_id,\n" +
            "    b.tour_name,\n" +
            "    b.price,\n" +
            "    o.order_date,\n" +
            "    o.total_amount,\n" +
            "    o.status\n" +
            "FROM\n" +
            "    user u\n" +
            "        JOIN\n" +
            "    user_order o ON u.user_id = o.user_id\n" +
            "        JOIN\n" +
            "    boutique_tour b ON o.boutique_id = b.boutique_id\n" +
            "WHERE\n" +
            "    u.nickname LIKE CONCAT('%', #{nickname}, '%')\n" + // 根据用户名查询
            "ORDER BY o.order_id")
    List<OrderUserBoutique> findOrderByUsername(String nickname);

    //根据用户ID查询用户订单
    @Select("SELECT\n" +
            "    o.order_id,\n" +
            "    b.tour_name,\n" +
            "    b.price,\n" +
            "    o.order_date,\n" +
            "    o.total_amount,\n" +
            "    o.status,\n" +
            "    b.image_url\n" + // 确保包含image_url字段
            "FROM\n" +
            "    user_order o\n" +
            "        JOIN\n" +
            "    boutique_tour b ON o.boutique_id = b.boutique_id\n" +
            "WHERE\n" +
            "    o.user_id = #{user_id}\n" +
            "ORDER BY o.order_id DESC")
    List<OrderUserBoutique> findOrderByUserId(Integer user_id);

    @Select("SELECT\n" +
            "    o.order_id,\n" +
            "    o.user_id,\n" +
            "    o.boutique_id,\n" +
            "    o.order_date,\n" +
            "    o.total_amount,\n" +
            "    o.status,\n" +
            "    b.tour_name,\n" +
            "    b.image_url as image_url\n" + // 确保返回image_url字段
            "FROM\n" +
            "    user_order o\n" +
            "    JOIN boutique_tour b ON o.boutique_id = b.boutique_id\n" +
            "WHERE\n" +
            "    o.order_id = #{order_id}")
    UserOrder findOrderById(Integer order_id);




    @Insert("INSERT INTO user_order (user_id, boutique_id, order_date, total_amount, status, image_url) VALUES (#{user_id}, #{boutique_id}, #{order_date}, #{total_amount}, '已付款', #{image_url})")
    int insertUserOrder(UserOrder userOrder);

    }
